package com.ets.Customer_DAO;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;

import com.ets.User_DAO.User;
import com.ets.common.DateUtils;
import com.ets.database.DB_Pipe;


/*------------------------------------------------------------------------------
 The <big><b>CustomerDB</b></big> class implements the reader & writer methods
 of the <b>CustomerDAO</b> interface to provide the data access to the
 <big><b>Customer</b></big> table
 <br><br>
 @author D.Merkling - 1:33:38 PM - Aug 22, 2011
 <br><br>
 @version 1.2  
------------------------------------------------------------------------------*/
public class CustomerDB extends DB_Pipe implements CustomerDAO
  {
	private Connection connection = null;
	
   public CustomerDB()
	 {
	   this.connect();
	 }
   /*
    * B.P.C Added Connect()
    */
      
   	private void connect()
       {
   	  //Connection con = null;
   	   //Class.forName("com.mysql.jdbc.Driver"); 
   	  try
   	    {
     	     Class.forName("com.mysql.jdbc.Driver");
   		 //String url      = "jdbc:mysql://localhost:3306/MYDB";
     	   String url      = "jdbc:mysql://localhost:3306/e_t_s";
     	  	 //String username = "root";
   		 String username = "ETS_Mstr";
   		 //String password = "rootpwd";
   		 String password = "ETS_Pswd";
   		 connection      = DriverManager.getConnection(url, username, password);
   		 System.out.println ("Database connection established");

   	    }
   	  catch(ClassNotFoundException e)
   	    {
   		 System.err.println("Database driver was not found.");
   	    }
   	  catch(SQLException e)
   	    {
   		 System.err.println("Error connecting to the database: " + e);
   	    }
   	 // return; 
       }   
    
   /*
    * End B.P.C added Connect()	
    */
   /*
    * Start B.P.C added disconnect (Connection con) * 	
    */
   		   private void disconnect (Connection con)
   		     {
   			  try
   			    {
   				 con.close(); 
   			    }
   			  catch(SQLException e)
   			    {
   				 System.err.println("Error disconnecting from the database: " + e);
   			    }
   		     }	
   /*
    * End B.P.C added disconnect (Connection con) * 	
   	*   	
   /*------------------------------------------------------------------------------
	Customer reader methods
   ------------------------------------------------------------------------------*/
	   
   public boolean existsCustomer (int cust)
     {
	  if (cust < 1)
	    {
		 return false;
	    }
	  try
		{
	     String exists = "SELECT Cust "      +
				           "FROM customer "  +
				          "WHERE Cust  = ?";
         DB_Pipe              = DB_Connect();
	     PreparedStatement ps = DB_Pipe.prepareStatement(exists);
		 ps.setInt(1, cust);
		 ResultSet         rs = ps.executeQuery();
		 if (rs.next())
		   {
		    rs.close();
		    ps.close();
		    return true;
		   }
		 else
		   {
		    rs.close();
		    ps.close();
		    return false;
		   }
		}
	  catch(SQLException sqle)
		{
		 //sqle.printStackTrace();	// for debugging
		 return false;
		}
	  finally
		{
		 DB_Disconnect(DB_Pipe);
		}
     }

   public boolean existsCustomer (String tin)
     {
	  String tmpTIN = tin.trim();
             tmpTIN = tmpTIN.toUpperCase();
	  try
		{
	     String exists = "SELECT Cust "      +
				           "FROM customer "  +
				          "WHERE TIN  = ?";
         DB_Pipe              = DB_Connect();
	     PreparedStatement ps = DB_Pipe.prepareStatement(exists);
		 ps.setString(1, tmpTIN);
		 ResultSet         rs = ps.executeQuery();
		 if (rs.next())
		   {
		    rs.close();
		    ps.close();
		    return true;
		   }
		 else
		   {
		    rs.close();
		    ps.close();
		    return false;
		   }
		}
	  catch(SQLException sqle)
		{
		 //sqle.printStackTrace();	// for debugging
		 return false;
		}
	  finally
		{
		 DB_Disconnect(DB_Pipe);
		}
     }
   
   public Customer getCustomer (int cust)
     {
	  try
		{
		 String sel = "SELECT Cust, "   +
		                     "TIN, " +
		                     "Nm_Fst, " +
		                     "Nm_Lst, " +
		                     "Addr, " +
		                     "Phn, "    +
				             "Phn2, "   +
				             "Email, "  +
				             "Email2 "  +
				        "FROM customer "    +
				       "WHERE Cust = ?";
         DB_Pipe              = DB_Connect();
		 PreparedStatement ps = DB_Pipe.prepareStatement(sel);
		 ps.setInt(1, cust);
		 ResultSet         rs = ps.executeQuery();
		 if (rs.next())
		   {
		    String TIN    = rs.getString("TIN");
		    String Nm_Fst = rs.getString("Nm_Fst");
		    String Nm_Lst = rs.getString("Nm_Lst");
		    int Addr      = rs.getInt("Addr");
		    String Phn1   = rs.getString("Phn");
		    String Phn2   = rs.getString("Phn2");
		    String Email  = rs.getString("Email");
		    String Email2 = rs.getString("Email2");
		    Customer c    = new Customer(cust, TIN, Nm_Fst, Nm_Lst, Addr, Phn1, Phn2, Email, Email2);
		    rs.close();
		    ps.close();
		    return c;
		   }
		 else
		   return null;
		}
	  catch(SQLException sqle)
		{
		 //sqle.printStackTrace();	// for debugging
		 return null;
		}
	  finally
		{
		 DB_Disconnect(DB_Pipe);
		}
     }
   
   public Customer getCustomer (String tin)
     {
	  try
		{
		 String sel = "SELECT Cust, "   +
		                     "TIN, " +
		                     "Nm_Fst, " +
		                     "Nm_Lst, " +
		                     "Addr, " +
		                     "Phn, "    +
				             "Phn2, "   +
				             "Email, "  +
				             "Email2 "  +
				        "FROM customer "    +
				       "WHERE tin = ?";
         DB_Pipe              = DB_Connect();
		 PreparedStatement ps = DB_Pipe.prepareStatement(sel);
		 ps.setString(1, tin);
		 ResultSet         rs = ps.executeQuery();
		 if (rs.next())
		   {
		    int    Customer = rs.getInt("Cust");
		    String Nm_Fst   = rs.getString("Nm_Fst");
		    String Nm_Lst   = rs.getString("Nm_Lst");
		    int Addr        = rs.getInt("Addr");
		    String Phn1     = rs.getString("Phn");
		    String Phn2     = rs.getString("Phn2");
		    String Email    = rs.getString("Email");
		    String Email2   = rs.getString("Email2");
		    Customer c      = new Customer(Customer, tin, Nm_Fst, Nm_Lst, Addr, Phn1, Phn2, Email, Email2);
		    rs.close();
		    ps.close();
		    return c;
		   }
		 else
		   return null;
		}
	  catch(SQLException sqle)
		{
		 //sqle.printStackTrace();	// for debugging
		 return null;
		}
	  finally
		{
		 DB_Disconnect(DB_Pipe);
		}
     }
   
   public boolean getCustomers (ArrayList<Customer> custs)
     {
	  try
		{
		 String sel = "SELECT Cust, "   +
		                     "TIN, " +
		                     "Nm_Fst, " +
		                     "Nm_Lst, " +
		                     "Addr, " +
		                     "Phn, "    +
				             "Phn2, "   +
				             "Email, "  +
				             "Email2 "  +
				        "FROM customer";
         DB_Pipe              = DB_Connect();
		 PreparedStatement ps = DB_Pipe.prepareStatement(sel);
		 ResultSet         rs = ps.executeQuery();
		 while (rs.next())
		   {
		    int    Customer = rs.getInt("Cust");
		    String TIN      = rs.getString("TIN");
		    String Nm_Fst   = rs.getString("Nm_Fst");
		    String Nm_Lst   = rs.getString("Nm_Lst");
		    int Addr        = rs.getInt("Addr");
		    String Phn1     = rs.getString("Phn");
		    String Phn2     = rs.getString("Phn2");
		    String Email    = rs.getString("Email");
		    String Email2   = rs.getString("Email2");
		    Customer c      = new Customer(Customer, TIN, Nm_Fst, Nm_Lst, Addr, Phn1, Phn2, Email, Email2);
		    custs.add(c);
		   }
	     rs.close();
		 ps.close();
		 return true;
		}
	  catch(SQLException sqle)
		{
		 //sqle.printStackTrace();	// for debugging
		 return false;
		}
	  finally
		{
		 DB_Disconnect(DB_Pipe);
		}
     }
   
   /*------------------------------------------------------------------------------
	Customer writer methods
   ------------------------------------------------------------------------------*/
	   
   public boolean addCustomer (Customer c)
     {
	  try
		{
		 String ins = "INSERT "                   +
		                  "INTO customer (Cust, " +
		                            "TIN, "    +
		                            "Nm_Fst, "    +
		                            "Nm_Lst, "    +
		                            "Addr, "    +
		                            "Phn, "       +
		                            "Phn2, "      +
		                            "Email, "     +
		                            "Email2, "    +
		                            "TMS_Updt, "  +
		                            "User_Updt, " +
		                            "TMS_Crtd, "  +
		                            "User_Crtd) " +
				      "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
         DB_Pipe              = DB_Connect();
		 PreparedStatement ps = DB_Pipe.prepareStatement(ins);
	     Timestamp         ts = DateUtils.getTmstmp();
		 ps.setInt(1, c.getCust());
		 ps.setString(2, c.getTIN());
		 ps.setString(3, c.getNmFst());
		 ps.setString(5, c.getNmLst());
		 ps.setInt(6, c.getAddr());
		 ps.setString(7, c.getPhn());
		 ps.setString(8, c.getPhn2());
		 ps.setString(9, c.getEmail());
		 ps.setString(10, c.getEmail2());
		 ps.setTimestamp(11, ts);
		 ps.setString(12, "USER1234");
		 ps.setTimestamp(13, ts);
		 ps.setString(14, "USER5678");
		 ps.executeUpdate();
		 ps.close();
		 return true;
		}
	  catch(SQLException sqle)
		{
		 //sqle.printStackTrace();	// for debugging
		 return false;
		}
	  finally
		{
		 DB_Disconnect(DB_Pipe);
		}
     }
   
   public boolean updateCustomer (Customer c)
     {
	  try
		{
		 String upd = "UPDATE customer "           +
		                 "SET TIN    = ?, " +
		                     "Nm_Fst    = ?, " +
		                     "Nm_Lst    = ?, " +
		                     "Addr      = ?, " +
		                     "Phn       = ?, " +
		                     "Phn2      = ?, " +
		                     "Email     = ?, " +
		                     "Email2    = ?, " +
		                     "TMS_Updt  = ?, " +
					         "User_Updt = ? "  +
				       "WHERE Customer = ?";
         DB_Pipe              = DB_Connect();
		 PreparedStatement ps = DB_Pipe.prepareStatement(upd);
	     Timestamp         ts = DateUtils.getTmstmp();
		 ps.setString(1, c.getTIN());
		 ps.setString(2, c.getNmFst());
		 ps.setString(4, c.getNmLst());
		 ps.setInt(5, c.getAddr());
		 ps.setString(6, c.getPhn());
		 ps.setString(7, c.getPhn2());
		 ps.setString(8, c.getEmail());
		 ps.setString(9, c.getEmail2());
		 ps.setTimestamp(10, ts);
		 ps.setString(11, "USER1234");
		 ps.executeUpdate();
		 ps.close();
		 return true;
		}
	  catch(SQLException sqle)
		{
		 //sqle.printStackTrace();	// for debugging
	 	 return false;
		}
	  finally
		{
		 DB_Disconnect(DB_Pipe);
		}
     }
   
   public boolean deleteCustomer (Customer c)
     {
	  try
		{
		 String del = "DELETE "         +
		                "FROM customer "    +
				       "WHERE Cust = ?";
         DB_Pipe              = DB_Connect();
		 PreparedStatement ps = DB_Pipe.prepareStatement(del);
		 ps.setInt(1, c.getCust());
		 ps.executeUpdate();
		 ps.close();
		 return true;
		}
	  catch(SQLException sqle)
		{
		 //sqle.printStackTrace();	// for debugging
		 return false;
		}  
	  finally
		{
		 DB_Disconnect(DB_Pipe);
		}
     }
   
   public ArrayList<Customer> GetCustomers()
   {
	  try
		{
		 ArrayList<Customer> custs = new ArrayList<Customer>();
		
		 String sel = "SELECT Cust, "   +
		                     "TIN, " +
		                     "Nm_Fst, " +
		                     "Nm_Lst, " +
		                     "Addr, " +
		                     "Phn, "    +
				             "Phn2, "   +
				             "Email, "  +
				             "Email2 "  +
				        "FROM customer";

		 PreparedStatement ps = null;
		 ps = connection.prepareStatement(sel);
		 ResultSet         rs = ps.executeQuery();
		 
		 while (rs.next())
		   {
		    int    Customer = rs.getInt("Cust");
		    String TIN      = rs.getString("TIN");
		    String Nm_Fst   = rs.getString("Nm_Fst");
		    String Nm_Lst   = rs.getString("Nm_Lst");
		    int Addr        = rs.getInt("Addr");
		    String Phn1     = rs.getString("Phn");
		    String Phn2     = rs.getString("Phn2");
		    String Email    = rs.getString("Email");
		    String Email2   = rs.getString("Email2");
		    Customer c      = new Customer(Customer, TIN, Nm_Fst, Nm_Lst, Addr, Phn1, Phn2, Email, Email2);
		    custs.add(c);
		   }
	     rs.close();
		 ps.close();
		 disconnect(connection);
		 return custs;
		}
	  catch(SQLException sqle)
		{
		 sqle.printStackTrace();	// for debugging
		 return null;
		}
   	}
  }
